package tingshu.repository;

import lombok.SneakyThrows;
import tingshu.data_object.AidToCountDO;
import tingshu.data_object.AlbumDO;
import tingshu.util.DBUtil;
import tingshu.util.Log;
import tingshu.view_object.CandidateTrackView;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author houdaiqin
 * @date 2022/07/17 15:15
 **/
public class AlbumRepo {
    //通过uid和aid从数据库中查找专辑内容
    @SneakyThrows
    public AlbumDO selectOneByUidAndAid(int uid,int aid) {
        String sql = "select aid,uid,title,cover,state from albums where uid=? and aid=?";
        try (Connection c = DBUtil.connection()) {
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);
                ps.setInt(2,aid);

                Log.println("执行SQL"+ps.toString());
                try(ResultSet rs=ps.executeQuery()){
                    if (!rs.next()){
                        return null;
                    }
                    return new AlbumDO(rs.getInt("aid"),rs.getInt("uid"),
                            rs.getString("title"),rs.getString("cover"),
                            rs.getInt("state"));
                }
            }
        }
    }

    //通过aidList查询出每个专辑关联的音频数
    @SneakyThrows
    public List<AidToCountDO> selectCountByAidListGroupByAidList(List<Integer> aidList){
        //%s是音频id的数组
        String sqlFormat="select aid,count(*) as ref_count from relations where aid in (%s) group by aid order by aid";
        StringBuilder sb=new StringBuilder();
        for(Integer tid:aidList){
            sb.append(tid).append(",");
        }
        sb.delete(sb.length() - 1, sb.length());
        String sql = String.format(sqlFormat, sb.toString());
        List<AidToCountDO> list=new ArrayList<>();
        try(Connection c= DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                Log.println("执行SQL"+ps.toString());

                try(ResultSet rs=ps.executeQuery()){
                    while (rs.next()){
                        AidToCountDO aidToCountDO=new AidToCountDO(
                                rs.getInt("aid"),
                                rs.getInt("ref_count")
                        );
                        list.add(aidToCountDO);
                    }
                }
            }
        }
        return list;
    }

    //通过uid获取专辑表albums的信息
    @SneakyThrows
    public List<AlbumDO> selectAlbumListByUid(int uid){
        String sql="select aid,title,cover,state from albums where uid=? order by aid";
        List<AlbumDO> list=new ArrayList<>();
        try(Connection c=DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);
                Log.println("执行SQL"+ps.toString());

                try(ResultSet rs=ps.executeQuery()){
                    while (rs.next()){
                        AlbumDO albumDO=new AlbumDO(
                                rs.getInt("aid"),
                                rs.getString("title"),
                                rs.getString("cover"),
                                rs.getInt("state")
                        );
                        list.add(albumDO);
                    }
                }
            }
        }
        return list;
    }

    //通过uid获取uid下的所有专辑id，返回一个aidList
    @SneakyThrows
    public List<Integer> selectAidListByUid(int uid){
        String sql="select aid from albums where uid=?";
        List<Integer> aidList=new ArrayList<>();
        try(Connection c=DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);
                Log.println("执行SQL"+ps.toString());

                try(ResultSet rs=ps.executeQuery()){
                    while (rs.next()){
                        aidList.add(rs.getInt("aid"));
                    }
                }
            }
        }
        return aidList;
    }

    //创建专辑
    @SneakyThrows
    public void insertInToAlbum(int uid, String title, String cover, int state){
        String sql="insert into albums (uid,title,cover,state) values (?,?,?,?)";
        try(Connection c=DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);
                ps.setString(2,title);
                ps.setString(3,cover);
                ps.setInt(4,state);

                Log.println("执行SQL"+ps.toString());
                ps.executeUpdate();
            }
        }
    }

    //通过aid查找出对应的用户id（uid）
    @SneakyThrows
    public int selectUidByAid(int aid){
        try(Connection c= DBUtil.connection()){
            String sql="select uid from albums where aid=?";
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1, aid);
                try(ResultSet rs=ps.executeQuery()){
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }
    @SneakyThrows
    public List<CandidateTrackView> selectTidAndTitleByUidAndAid(int uid,int aid){
        List<CandidateTrackView> list=new ArrayList<>();
        try(Connection c= DBUtil.connection()){
            //查找出该专辑没有绑定过的音频
            String sql="select tid,title from tracks where uid=? and tid not in (select tid from relations where aid=?) order by tid";
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);
                ps.setInt(2,aid);
                Log.println("执行SQL"+ps.toString());

                try(ResultSet rs=ps.executeQuery()){
                    while (rs.next()){
                        CandidateTrackView trackView=new CandidateTrackView(
                                rs.getInt("tid"),
                                rs.getString("title")
                        );
                        list.add(trackView);
                    }
                }
            }
        }
        return list;
    }
}